<?php
class front_m_job extends Zmodel{
	function get_company_by_comid($comid){
		$this->load->database();
		$sql="SELECT *
            	FROM companies
               WHERE com_id=".$comid;
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function get_announ_by_announid($announid){
		$this->load->database();
		$sql="SELECT * FROM announcements WHERE announ_id=".$announid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
function get_allannoun_by_comid($comid,$num, $offset){
		$this->load->database();
		$sqlstatement="SELECT a.announ_id,
		        			 c.com_id,
		        			 c.com_name,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo,
		            		 c.com_logo_type,
		        			 a.date_close
		            	FROM companies AS c 
          		  INNER JOIN announcements AS a 
          		  		  ON c.com_id=a.com_id 
          		  	   WHERE c.com_id=".$comid." 
          		  	ORDER BY a.announ_id DESC 
          		   LIMIT ".$num;
		if($offset==""){
             $sql=$sqlstatement." OFFSET 0";
        }else {
        	$sql=$sqlstatement." OFFSET ". $offset;
        }
        return $this->db->query($sql);
	}
	function get_allannoun(){
		$this->load->database();
        $sql="SELECT a.announ_id,
        			 c.com_id,
        			 c.com_name,
        			 c.com_desc,
            		 c.com_logo_size,
            		 c.com_logo_name,
            		 c.com_logo,
            		 c.com_logo_type,
        			 a.date_post,
        			 a.date_close
            	FROM announcements AS a 
          INNER JOIN companies AS c 
          		  ON a.com_id=c.com_id 
          		  ORDER BY a.announ_id DESC 
          		  LIMIT 5";
        return $this->db->query($sql);
	}
	function get_allannoun_front($num, $offset){
		$this->load->database();
		$sqlstatement="SELECT a.announ_id,
		        			 c.com_id,
		        			 c.com_name,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo,
		            		 c.com_logo_type,
		        			 a.date_close
		            	FROM companies AS c 
	          INNER JOIN announcements AS a 
	          		  ON c.com_id=a.com_id 
	          		  ORDER BY a.announ_id DESC
          		   LIMIT ".$num;
		if($offset==""){
            $sql=$sqlstatement." OFFSET 0";
        }else {
        	 $sql=$sqlstatement." OFFSET ". $offset;
        }
        return $this->db->query($sql);
	}
	function get_title_by_announid($announid){
		$this->load->database();
		$sql="SELECT *
            	FROM titles
               WHERE announ_id=".$announid;
        	return $this->db->query($sql);
	}
function get_title_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT *
            	FROM titles
               WHERE title_id=".$titleid." ORDER BY title_id ASC";
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_jobCategory(){
		$this->load->database();
		$sql="SELECT categories.cate_id, categories.cate_desc FROM categories;";
		return $this->db->query($sql);
	}
	function get_category_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT c.cate_id,
					 c.cate_desc 
				FROM categories AS c 
		  INNER JOIN titles_categories as t 
			      ON c.cate_id=t.cate_id
			   WHERE t.title_id=".$titleid;
		return $this->db->query($sql);
	}
	function get_all_job_location(){
		$this->load->database();
		$sql="SELECT loc_id,
					 loc_desc
				FROM locations";
		return $this->db->query($sql);
	}
	function get_location_by_announid($announid){
		$this->load->database();
		$sql="SELECT l.loc_id,
					 l.loc_desc
				FROM locations as l
		  INNER JOIN titles_locations as tl
				  ON l.loc_id=tl.loc_id
		  INNER JOIN titles as t 
				  ON tl.title_id=t.title_id
			   WHERE t.announ_id=".$announid;
		return $this->db->query($sql);
	}
function get_location_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT l.loc_id,
					 l.loc_desc
				FROM locations as l
		  INNER JOIN titles_locations as t
				  ON l.loc_id=t.loc_id
			   WHERE t.title_id=".$titleid;
		return $this->db->query($sql);
	}
	function get_requirement_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT * 
				FROM requirements 
			   WHERE title_id=".$titleid." ORDER BY req_id ASC";
		return $this->db->query($sql);
	}
	function get_duty_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT * 
				FROM duties
			   WHERE title_id=".$titleid." ORDER BY duty_id ASC";
		return $this->db->query($sql);
	}
	function get_schedule_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT s.schedule_id,
					 s.schedule_desc 
				FROM schedules as s 
		  INNER JOIN titles_schedules as t 
				  ON s.schedule_id=t.schedule_id 
			   WHERE t.title_id=".$titleid." ORDER BY s.schedule_id ASC";
		return $this->db->query($sql);
	}
	function count_all_title(){
		$this->load->database();
		$sql="SELECT title_id FROM titles";
		return $this->db->query($sql);
	}
function get_hit_job($announid){
		$this->load->database();
		$sql="SELECT *
				FROM hit_jobs WHERE announ_id=".$announid;
		return $this->db->query($sql);
	}
	function addnew_hit_job($announid){
		$this->load->database();
		$sql="INSERT INTO hit_jobs(announ_id) VALUES(".$announid.")";
		$this->db->query($sql);
	}
	function delete_hit_job($announid){
		$this->load->database();
		$sql="DELETE FROM hit_jobs
				   WHERE  announ_id=".$announid;
		$this->db->query($sql);
	}
	
	//======search==========
	function count_search_result($category,$location){
		$this->load->database();
		$sqlstatement="SELECT DISTINCT a.announ_id
            					  FROM announcements AS a 
          					INNER JOIN titles AS t 
          		 				    ON a.announ_id=t.announ_id 
         				    INNER JOIN titles_categories as tc
          		  				    ON t.title_id=tc.title_id
          					INNER JOIN titles_locations as tl
          		 				    ON t.title_id=tl.title_id ";
		if($category=='0' && $location=='0')
		{
			$sql=$sqlstatement;
		}
		elseif($category=='0' && $location<>'0')
		{
			$sql=$sqlstatement."WHERE tl.loc_id=".$location;
		}
		elseif($category<>'0' && $location=='0')
		{
			$sql=$sqlstatement."WHERE tc.cate_id=".$category;
		}
		else
		{//if($cate<>'0' && $location<>'0')
			$sql=$sqlstatement."WHERE tc.cate_id=".$category." AND tl.loc_id=".$location;
		}
        return $this->db->query($sql);
	}
	function search_job_result($category,$location,$num, $offset){
		$this->load->database();
		
		$sqlstatement="SELECT DISTINCT a.announ_id,
				        	  c.com_id,
				        	  c.com_name,
				        	  c.com_logo,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo_type,
				        	  a.date_close
				         FROM companies AS c 
				   INNER JOIN announcements AS a 
				           ON c.com_id=a.com_id 
				   INNER JOIN titles AS t 
				           ON a.announ_id=t.announ_id 
				   INNER JOIN titles_categories as tc
				   		   ON t.title_id=tc.title_id
				   INNER JOIN titles_locations as tl
				           ON t.title_id=tl.title_id ";
          	  
		if($offset==""){
			if($category=='0' && $location=='0')
			{
				$sql=$sqlstatement."ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
			elseif($category=='0' && $location<>'0'){
				$sql=$sqlstatement."WHERE tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
			elseif($category<>'0' && $location=='0'){
				$sql=$sqlstatement."WHERE tc.cate_id=".$category." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
			else{//if($cate<>'0' && $location<>'0')
				$sql=$sqlstatement."WHERE tc.cate_id=".$category." AND tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
        }else {//$offset <> ''
        	if($category=='0' && $location=='0'){
					$sql=$sqlstatement."ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;
				}
				elseif($category=='0' && $location<>'0'){
					$sql=$sqlstatement."WHERE tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;;
				}
				elseif($category<>'0' && $location=='0'){
					$sql=$sqlstatement."WHERE tc.cate_id=".$category." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;;
				}
				else{//if($cate<>'0' && $location<>'0')
					$sql=$sqlstatement."WHERE tc.cate_id=".$category." AND tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;;
				}
        }
        return $this->db->query($sql);
	}
}

?>